﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;
using CrystalDecisions.CrystalReports.Engine;
using CrystalDecisions.Shared;

namespace quanlysinhvien
{
    public partial class FrmReport : Form
    {
        SqlConnection conn;
        SqlCommand cmd;
        SqlDataAdapter da;
        DataTable dt;
        DataSet ds;
        public FrmReport()
        {
            InitializeComponent();
        }
        private void LoadcbSubject()
        {
            conn = Connect.getConnection();
            conn.Open();
            string sql = "select * from TblSubject";
            cmd = new SqlCommand(sql, conn);
            da = new SqlDataAdapter(cmd);
            dt = new DataTable();
            da.Fill(dt);
            cbSubject.DataSource = dt;
            cbSubject.DisplayMember = "Subject_Name";
            cbSubject.ValueMember = "Subject_Code";
            da.Dispose();
            conn.Close();
            cbSubject.Text = "Select Subject";
        }
        private void LoadFaculty()
        {
            conn = Connect.getConnection();
            conn.Open();
            string sql = "select * from TblFaculty";
            cmd = new SqlCommand(sql, conn);
            da = new SqlDataAdapter(cmd);
            dt = new DataTable();
            da.Fill(dt);
            cbFaculty.DataSource = dt;
            cbFaculty.DisplayMember = "Faculty_Name";
            cbFaculty.ValueMember = "Faculty_Code";
            da.Dispose();
            conn.Close();
            cbFaculty.Text = "Select Faculty";
        }
        private void LoadClass()
        {
            string fac = cbFaculty.SelectedValue.ToString();
            conn = Connect.getConnection();
            conn.Open();
            string sql = "select * from TblClass where Faculty_Code='" + fac + "'";
            cmd = new SqlCommand(sql, conn);
            da = new SqlDataAdapter(cmd);
            dt = new DataTable();
            da.Fill(dt);
            cbClass.DataSource = dt;
            cbClass.DisplayMember = "Class_Code";
            cbClass.ValueMember = "Class_Code";
            da.Dispose();
            conn.Close();
            cbClass.Text = "Select Class";
        }
        private void FrmReport_Load(object sender, EventArgs e)
        {
            LoadcbSubject();
            LoadFaculty();
            
        }

        private void cbFaculty_SelectedIndexChanged(object sender, EventArgs e)
        {
            LoadClass();
        }

        private void btnFaculty_Click(object sender, EventArgs e)
        {
            string sub1 = cbSubject.SelectedValue.ToString();
            string fac1 = cbFaculty.SelectedValue.ToString();
            conn = Connect.getConnection();
            conn.Open();
            string sql = " select TblStu_Sub.Student_Code,TblClass.Class_Code,TblStudents.Student_Name,TblStudents.Student_DOB,TblStudents.Student_Sex,TblStu_Sub.Mark,TblFaculty.Faculty_Code from TblStudents,TblClass,TblStu_Sub,TblFaculty where TblStu_Sub.Student_Code=TblStudents.Student_Code and TblClass.Class_Code=TblStudents.Class_Code and TblFaculty.Faculty_Code=TblClass.Faculty_Code and TblStu_Sub.Subject_Code='" + sub1 + "' and TblFaculty.Faculty_Code='" + fac1 + "'";
            cmd = new SqlCommand(sql, conn);
            da = new SqlDataAdapter(cmd);
            ds = new DataSet();
            da.Fill(ds,"fac");
            CRReport1 crr = new CRReport1();
            crr.SetDataSource(ds);
            cryReportview.ReportSource = crr;
            cryReportview.Show();
            cryReportview.Refresh();
        }

        private void btnClass_Click(object sender, EventArgs e)
        {
            string sub2 = cbSubject.SelectedValue.ToString();
            string clas1 = cbClass.SelectedValue.ToString();
            conn = Connect.getConnection();
            conn.Open();
            string sql = " select TblStu_Sub.Student_Code,TblClass.Class_Code,TblStudents.Student_Name,TblStudents.Student_DOB,TblStudents.Student_Sex,TblStu_Sub.Mark from TblStudents,TblClass,TblStu_Sub where TblStu_Sub.Student_Code=TblStudents.Student_Code and TblClass.Class_Code=TblStudents.Class_Code and TblStu_Sub.Subject_Code='" + sub2 + "' and TblStudents.Class_Code='" + clas1 + "'";
            cmd = new SqlCommand(sql, conn);
            da = new SqlDataAdapter(cmd);
            ds = new DataSet();
            da.Fill(ds, "clas");
            CRReport1 crr2 = new CRReport1();
            crr2.SetDataSource(ds);
            cryReportview.ReportSource = crr2;
            cryReportview.Show();
            cryReportview.Refresh();
        }

        private void btnBack_Click(object sender, EventArgs e)
        {
            this.Hide();
        }

        private void button1_Click(object sender, EventArgs e)
        {

            conn = Connect.getConnection();
            conn.Open();
            string sql = " select TblStu_Sub.Student_Code,TblClass.Class_Code,TblStudents.Student_Name,TblStudents.Student_DOB,TblStudents.Student_Sex,TblStu_Sub.Mark from TblStudents,TblClass,TblStu_Sub where TblStu_Sub.Student_Code=TblStudents.Student_Code and TblClass.Class_Code=TblStudents.Class_Code ";
            cmd = new SqlCommand(sql, conn);
            da = new SqlDataAdapter(cmd);
            ds = new DataSet();
            da.Fill(ds, "clas");
            CRReport1 crr2 = new CRReport1();
            crr2.SetDataSource(ds);
            cryReportview.ReportSource = crr2;
            cryReportview.Show();
            cryReportview.Refresh();
        }
    }
}
